/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */

package controller;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Level;
import java.util.logging.Logger;
import javax.swing.JOptionPane;
import model.Dosis;
import model.EfekSamping;
import model.Indikasi;
import model.KategoriPenyakit;
import model.Kemasan;
import model.Komposisi;
import model.KontraIndikasi;
import model.Obat;
import model.Perhatian;
import model.PunyaDosis;
import model.PunyaEfekSamping;
import model.PunyaIndikasi;
import model.PunyaKemasan;
import model.PunyaKomposisi;
import model.PunyaKontraIndikasi;
import model.PunyaPerhatian;

/**
 *
 * @author Asus
 */
public class AdminKontrol {
 private Connection conn;

    public AdminKontrol(Connection koneksi) {
        this.conn = koneksi;
    }

    public static AdminKontrol getKoneksiKategori() throws SQLException {
        AdminKontrol kon = new AdminKontrol(OraConnection.getKoneksi());
        return kon;
    }



    public long carikodeobat(String kode) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;
        long kodebarang=0;
        String a="";
        try {
            conn.setAutoCommit(false);
            String sql = "select max(kodeobat) from obat where kodekategori like '" + kode + "'";
            statement = conn.prepareStatement(sql);
            result = statement.executeQuery();
            while (result.next()) {
                a= result.getString("max(kodeobat)");
                if(a==null){
                    kodebarang=0;
                    break;
                }
                String b = a.substring(3,6);
                kodebarang=Long.parseLong(b);
            }
            conn.commit();
        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        kodebarang= kodebarang + 1;
        return kodebarang;
    }

    public List<Obat> cariObat(String cari) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select obat.kodeobat, obat.namaobat, kategoripenyakit.kodekategori, "
                    + "kategoripenyakit.namakategori "
                    + "from obat,kategoripenyakit "
                    + "where obat.kodekategori=kategoripenyakit.kodekategori and obat.namaobat like '"+cari+"'");
            result = statement.executeQuery();
            List<Obat> obat = new ArrayList<Obat>();
            while (result.next()) {
                Obat ob = new Obat();
                KategoriPenyakit kat=new KategoriPenyakit();
                ob.setKodeObat(result.getString("kodeobat"));
                ob.setNamaObat(result.getString("namaobat"));
                kat.setKodeKategori(result.getString("kodekategori"));
                kat.setNamaKategori(result.getString("namakategori"));
                ob.setKategori(kat);
                obat.add(ob);
            }
            conn.commit();
            return obat;
    }
public int cekKomposisi(PunyaKomposisi kom) throws SQLException {
        Statement statement = null;
        ResultSet result = null;
        int a = 0;
        try {
            statement = conn.createStatement();
            String sql = "select komposisi.idkomposisi, komposisi.namakomposisi "
                    + "from obat,komposisi,punyakomposisi "
                    + "where obat.kodeobat=punyakomposisi.kodeobat "
                    + "and komposisi.idkomposisi=punyakomposisi.idkomposisi "
                    + "and obat.kodeobat='"+kom.getObat().getKodeObat()+"' "
                    + "and komposisi.idkomposisi='"+kom.getKomposisi().getIdKomposisi()+"'";
            result = statement.executeQuery(sql);
            while (result.next()) {
                String a1 = (result.getString("namakomposisi"));
                 if (a1 == null) {
                    a = 0;
                    break;
                }
                a++;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        return a;

    }
    public int cekIndikasi(PunyaIndikasi in) throws SQLException {
        Statement statement = null;
        ResultSet result = null;
        int a = 0;
        try {
            statement = conn.createStatement();
            String sql = "select indikasi.idindikasi, indikasi.namaindikasi "
                    + "from obat,indikasi,punyaindikasi "
                    + "where obat.kodeobat=punyaindikasi.kodeobat "
                    + "and indikasi.idindikasi=punyaindikasi.idindikasi "
                    + "and obat.kodeobat='"+in.getObat().getKodeObat()+"' "
                    + "and indikasi.idindikasi='"+in.getIndikasi().getIdIndikasi()+"'";
            result = statement.executeQuery(sql);
            while (result.next()) {
                String a1 = (result.getString("namaindikasi"));
                 if (a1 == null) {
                    a = 0;
                    break;
                }
                a++;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        return a;

    }

    public int cekKontraIndikasi(PunyaKontraIndikasi in) throws SQLException {
        Statement statement = null;
        ResultSet result = null;
        int a = 0;
        try {
            statement = conn.createStatement();
            String sql = "select kontraindikasi.idkontraindikasi, kontraindikasi.namakontraindikasi "
                    + "from obat,kontraindikasi,punyakontraindikasi "
                    + "where obat.kodeobat=punyakontraindikasi.kodeobat "
                    + "and kontraindikasi.idkontraindikasi=punyakontraindikasi.idkontraindikasi "
                    + "and obat.kodeobat='"+in.getObat().getKodeObat()+"' "
                    + "and kontraindikasi.idkontraindikasi='"+in.getKontraIndikasi().getIdKontraIndikasi()+"'";
            result = statement.executeQuery(sql);
            while (result.next()) {
                String a1 = (result.getString("namakontraindikasi"));
                 if (a1 == null) {
                    a = 0;
                    break;
                }
                a++;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        return a;

    }

    public int cekPerhatian(PunyaPerhatian in) throws SQLException {
        Statement statement = null;
        ResultSet result = null;
        int a = 0;
        try {
            statement = conn.createStatement();
            String sql = "select perhatian.idperhatian, perhatian.namaperhatian "
                    + "from obat,perhatian,punyaperhatian "
                    + "where obat.kodeobat=punyaperhatian.kodeobat "
                    + "and perhatian.idperhatian=punyaperhatian.idperhatian "
                    + "and obat.kodeobat='"+in.getObat().getKodeObat()+"' "
                    + "and perhatian.idperhatian='"+in.getPerhatian().getIdPerhatian()+"'";
            result = statement.executeQuery(sql);
            while (result.next()) {
                String a1 = (result.getString("namaperhatian"));
                 if (a1 == null) {
                    a = 0;
                    break;
                }
                a++;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        return a;

    }

    public int cekEfek(PunyaEfekSamping in) throws SQLException {
        Statement statement = null;
        ResultSet result = null;
        int a = 0;
        try {
            statement = conn.createStatement();
            String sql = "select efeksamping.idefeksamping, efeksamping.namaefeksamping "
                    + "from obat,efeksamping,punyaefeksamping "
                    + "where obat.kodeobat=punyaefeksamping.kodeobat "
                    + "and efeksamping.idefeksamping=punyaefeksamping.idefeksamping "
                    + "and obat.kodeobat='"+in.getObat().getKodeObat()+"' "
                    + "and efeksamping.idefeksamping='"+in.getEfekSamping().getIdEfekSamping()+"'";
            result = statement.executeQuery(sql);
            while (result.next()) {
                String a1 = (result.getString("namaefeksamping"));
                 if (a1 == null) {
                    a = 0;
                    break;
                }
                a++;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        return a;

    }

    public int cekKemasan(PunyaKemasan in) throws SQLException {
        Statement statement = null;
        ResultSet result = null;
        int a = 0;
        try {
            statement = conn.createStatement();
            String sql = "select kemasan.idkemasan, kemasan.namakemasan "
                    + "from obat,kemasan,punyakemasan "
                    + "where obat.kodeobat=punyakemasan.kodeobat "
                    + "and kemasan.idkemasan=punyakemasan.idkemasan "
                    + "and obat.kodeobat='"+in.getObat().getKodeObat()+"' "
                    + "and kemasan.idkemasan='"+in.getKemasan().getIdKemasan()+"'";
            result = statement.executeQuery(sql);
            while (result.next()) {
                String a1 = (result.getString("namakemasan"));
                 if (a1 == null) {
                    a = 0;
                    break;
                }
                a++;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        return a;

    }

    public int cekDosis(PunyaDosis in) throws SQLException {
        Statement statement = null;
        ResultSet result = null;
        int a = 0;
        try {
            statement = conn.createStatement();
            String sql = "select dosis.iddosis, dosis.namadosis "
                    + "from obat,dosis,punyadosis "
                    + "where obat.kodeobat=punyadosis.kodeobat "
                    + "and dosis.iddosis=punyadosis.iddosis "
                    + "and obat.kodeobat='"+in.getObat().getKodeObat()+"' "
                    + "and dosis.iddosis='"+in.getDosis().getIdDosis()+"'";
            result = statement.executeQuery(sql);
            while (result.next()) {
                String a1 = (result.getString("namadosis"));
                 if (a1 == null) {
                    a = 0;
                    break;
                }
                a++;
            }
            conn.close();

        } catch (SQLException ex) {
            Logger.getLogger(AdminKontrol.class.getName()).log(Level.SEVERE, null, ex);
        }
        return a;

    }

    public void tambahObat(Obat obat) throws SQLException {
        String kode = obat.getKodeObat();
        String nama = obat.getNamaObat();
        String kategori = obat.getKategori().getKodeKategori();
        String sql = "insert into obat (kodeobat, namaobat,kodekategori)"
                + " values ('" + kode + "','" + nama + "','" + kategori + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    public void tambahKomposisi(PunyaKomposisi kom) throws SQLException {
        String obat = kom.getObat().getKodeObat();
        String komposisi = kom.getKomposisi().getIdKomposisi();;
        String sql = "insert into punyakomposisi"
                + " values ('" + obat + "','" + komposisi + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
     public void tambahIndikasi(PunyaIndikasi in) throws SQLException {
        String obat = in.getObat().getKodeObat();
        String indikasi = in.getIndikasi().getIdIndikasi();
        String sql = "insert into punyaindikasi"
                + " values ('" + obat + "','" + indikasi + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    public void tambahKontraIndikasi(PunyaKontraIndikasi kon) throws SQLException {
        String obat = kon.getObat().getKodeObat();
        String konindikasi = kon.getKontraIndikasi().getIdKontraIndikasi();
        String sql = "insert into punyakontraindikasi"
                + " values ('" + obat + "','" + konindikasi + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void tambahPerhatian(PunyaPerhatian per) throws SQLException {
        String obat = per.getObat().getKodeObat();
        String perhatian = per.getPerhatian().getIdPerhatian();
        String sql = "insert into punyaperhatian"
                + " values ('" + obat + "','" + perhatian + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    public void tambahefek(PunyaEfekSamping efek) throws SQLException {
        String obat = efek.getObat().getKodeObat();
        String efe = efek.getEfekSamping().getIdEfekSamping();
        String sql = "insert into punyaefeksamping"
                + " values ('" + obat + "','" + efe + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void tambahkemasan(PunyaKemasan kem) throws SQLException {
        String obat = kem.getObat().getKodeObat();
        String kemas = kem.getKemasan().getIdKemasan();
        String sql = "insert into punyakemasan"
                + " values ('" + obat + "','" + kemas + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void tambahdosis(PunyaDosis dos) throws SQLException {
        String obat = dos.getObat().getKodeObat();
        String dosis = dos.getDosis().getIdDosis();
        String sql = "insert into punyadosis"
                + " values ('" + obat + "','" + dosis + "')";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }


    public List<Komposisi> lihatKomposisi() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select idkomposisi,"
                    + "namakomposisi from komposisi order by idKomposisi");
            result = statement.executeQuery();
            List<Komposisi> kom = new ArrayList<Komposisi>();
            while (result.next()) {
                Komposisi ko = new Komposisi();
                ko.setIdKomposisi(result.getString("idKomposisi"));
                ko.setNamaKomposisi(result.getString("namakomposisi"));
                kom.add(ko);
            }
            conn.commit();
            return kom;
    }
    public List<Komposisi> lihatKomposisiTerpilih(String a) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select komposisi.namakomposisi,"
                    + "komposisi.idkomposisi"
            + " from obat,punyakomposisi,komposisi "
            + "where obat.kodeobat=punyakomposisi.kodeobat "
            + "and punyakomposisi.idkomposisi= komposisi.idkomposisi "
            + "and punyakomposisi.kodeobat='"+a+"'");
            result = statement.executeQuery();
            List<Komposisi> kom = new ArrayList<Komposisi>();
            while (result.next()) {
                Komposisi ko = new Komposisi();
                ko.setNamaKomposisi(result.getString("namakomposisi"));
                ko.setIdKomposisi(result.getString("idkomposisi"));
                kom.add(ko);
            }
            conn.commit();
            return kom;
    }

    public List<Indikasi> lihatIndikasi() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select idindikasi,"
                    + "namaindikasi from indikasi order by idindikasi");
            result = statement.executeQuery();
            List<Indikasi> in = new ArrayList<Indikasi>();
            while (result.next()) {
                Indikasi ind = new Indikasi();
                ind.setIdIndikasi(result.getString("idindikasi"));
                ind.setNamaIndikasi(result.getString("namaindikasi"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }
    public List<Indikasi> lihatIndikasiTerpilih(String a) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select indikasi.namaindikasi,"
                    + "indikasi.idindikasi"
            + " from obat,punyaindikasi,indikasi "
            + "where obat.kodeobat=punyaindikasi.kodeobat "
            + "and punyaindikasi.idindikasi= indikasi.idindikasi "
            + "and punyaindikasi.kodeobat='"+a+"'");
            result = statement.executeQuery();
            List<Indikasi> in = new ArrayList<Indikasi>();
            while (result.next()) {
                Indikasi ind = new Indikasi();
                ind.setNamaIndikasi(result.getString("namaindikasi"));
                ind.setIdIndikasi(result.getString("idindikasi"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }

    public List<KontraIndikasi> lihatKontraIndikasi() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select idkontraindikasi,"
                    + "namakontraindikasi from kontraindikasi order by idkontraindikasi");
            result = statement.executeQuery();
            List<KontraIndikasi> in = new ArrayList<KontraIndikasi>();
            while (result.next()) {
                KontraIndikasi ind = new KontraIndikasi();
                ind.setIdKontraIndikasi(result.getString("idkontraindikasi"));
                ind.setNamaKontraIndikasi(result.getString("namakontraindikasi"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }
    public List<KontraIndikasi> lihatKontraIndikasiTerpilih(String a) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select kontraindikasi.namakontraindikasi,"
                    + "kontraindikasi.idkontraindikasi"
            + " from obat,punyakontraindikasi,kontraindikasi "
            + "where obat.kodeobat=punyakontraindikasi.kodeobat "
            + "and punyakontraindikasi.idkontraindikasi= kontraindikasi.idkontraindikasi "
            + "and punyakontraindikasi.kodeobat='"+a+"'");
            result = statement.executeQuery();
            List<KontraIndikasi> in = new ArrayList<KontraIndikasi>();
            while (result.next()) {
                KontraIndikasi ind = new KontraIndikasi();
                ind.setNamaKontraIndikasi(result.getString("namakontraindikasi"));
                ind.setIdKontraIndikasi(result.getString("idkontraindikasi"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }

    public List<Perhatian> lihatPerhatian() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select idperhatian,"
                    + "namaperhatian from perhatian order by idperhatian");
            result = statement.executeQuery();
            List<Perhatian> in = new ArrayList<Perhatian>();
            while (result.next()) {
                Perhatian ind = new Perhatian();
                ind.setIdPerhatian(result.getString("idperhatian"));
                ind.setNamaPerhatian(result.getString("namaperhatian"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }
    public List<Perhatian> lihatPerhatianTerpilih(String a) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select perhatian.namaperhatian,"
                    + "perhatian.idperhatian"
            + " from obat,punyaperhatian,perhatian "
            + "where obat.kodeobat=punyaperhatian.kodeobat "
            + "and punyaperhatian.idperhatian= perhatian.idperhatian "
            + "and punyaperhatian.kodeobat='"+a+"'");
            result = statement.executeQuery();
            List<Perhatian> in = new ArrayList<Perhatian>();
            while (result.next()) {
                Perhatian ind = new Perhatian();
                ind.setNamaPerhatian(result.getString("namaperhatian"));
                ind.setIdPerhatian(result.getString("idperhatian"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }

    public List<EfekSamping> lihatEfek() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select idefeksamping,"
                    + "namaefeksamping from efeksamping order by idefeksamping");
            result = statement.executeQuery();
            List<EfekSamping> in = new ArrayList<EfekSamping>();
            while (result.next()) {
                EfekSamping ind = new EfekSamping();
                ind.setIdEfekSamping(result.getString("idefeksamping"));
                ind.setNamaEfekSamping(result.getString("namaefeksamping"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }
    public List<EfekSamping> lihatEfekTerpilih(String a) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select efeksamping.namaefeksamping,"
                    + "efeksamping.idefeksamping"
            + " from obat,punyaefeksamping,efeksamping "
            + "where obat.kodeobat=punyaefeksamping.kodeobat "
            + "and punyaefeksamping.idefeksamping= efeksamping.idefeksamping "
            + "and punyaefeksamping.kodeobat='"+a+"'");
            result = statement.executeQuery();
            List<EfekSamping> in = new ArrayList<EfekSamping>();
            while (result.next()) {
                EfekSamping ind = new EfekSamping();
                ind.setNamaEfekSamping(result.getString("namaefeksamping"));
                ind.setIdEfekSamping(result.getString("idefeksamping"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }

    public List<Kemasan> lihatKemasan() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select idkemasan,"
                    + "namakemasan from kemasan order by idkemasan");
            result = statement.executeQuery();
            List<Kemasan> in = new ArrayList<Kemasan>();
            while (result.next()) {
                Kemasan ind = new Kemasan();
                ind.setIdKemasan(result.getString("idkemasan"));
                ind.setNamaKemasan(result.getString("namakemasan"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }
    public List<Kemasan> lihatKemasanTerpilih(String a) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select kemasan.namakemasan,"
                    + "kemasan.idkemasan"
            + " from obat,punyakemasan,kemasan "
            + "where obat.kodeobat=punyakemasan.kodeobat "
            + "and punyakemasan.idkemasan= kemasan.idkemasan "
            + "and punyakemasan.kodeobat='"+a+"'");
            result = statement.executeQuery();
            List<Kemasan> in = new ArrayList<Kemasan>();
            while (result.next()) {
                Kemasan ind = new Kemasan();
                ind.setNamaKemasan(result.getString("namakemasan"));
                ind.setIdKemasan(result.getString("idkemasan"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }

    public List<Dosis> lihatDosis() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select iddosis,"
                    + "namadosis from dosis order by iddosis");
            result = statement.executeQuery();
            List<Dosis> in = new ArrayList<Dosis>();
            while (result.next()) {
                Dosis ind = new Dosis();
                ind.setIdDosis(result.getString("iddosis"));
                ind.setNamaDosis(result.getString("namadosis"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }
    public List<Dosis> lihatDosisTerpilih(String a) throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select dosis.namadosis,"
                    + "dosis.iddosis"
            + " from obat,punyadosis,dosis "
            + "where obat.kodeobat=punyadosis.kodeobat "
            + "and punyadosis.iddosis= dosis.iddosis "
            + "and punyadosis.kodeobat='"+a+"'");
            result = statement.executeQuery();
            List<Dosis> in = new ArrayList<Dosis>();
            while (result.next()) {
                Dosis ind = new Dosis();
                ind.setNamaDosis(result.getString("namadosis"));
                ind.setIdDosis(result.getString("iddosis"));
                in.add(ind);
            }
            conn.commit();
            return in;
    }

    public List<KategoriPenyakit> lihatSeluruhKategori() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select kodekategori,"
                    + "namakategori from kategoripenyakit order by kodekategori");
            result = statement.executeQuery();
            List<KategoriPenyakit> kategoris = new ArrayList<KategoriPenyakit>();
            while (result.next()) {
                KategoriPenyakit kategori = new KategoriPenyakit();
                kategori.setKodeKategori(result.getString("kodekategori"));
                kategori.setNamaKategori(result.getString("namakategori"));
                kategoris.add(kategori);
            }
            conn.commit();
            return kategoris;
    }

    public List<Obat> lihatSeluruhObat() throws SQLException {
        PreparedStatement statement = null;
        ResultSet result = null;

            conn.setAutoCommit(false);
            statement = conn.prepareStatement("select obat.kodeobat, obat.namaobat, "
                    + "kategoripenyakit.kodekategori, kategoripenyakit.namakategori from obat,kategoripenyakit "
                    + "where obat.kodekategori= kategoripenyakit.kodekategori ");
            result = statement.executeQuery();
            List<Obat> ob = new ArrayList<Obat>();
            while (result.next()) {
                Obat obat = new Obat();
                KategoriPenyakit kat = new KategoriPenyakit();
                obat.setKodeObat(result.getString("kodeobat"));
                obat.setNamaObat(result.getString("namaobat"));
                kat.setKodeKategori(result.getString("kodekategori"));
                kat.setNamaKategori(result.getString("namakategori"));
                obat.setKategori(kat);
                ob.add(obat);
            }
            conn.commit();
            return ob;
    }

    public void deleteKomposisi(PunyaKomposisi ko) throws SQLException {
        String obat = ko.getObat().getKodeObat();
        String kom = ko.getKomposisi().getIdKomposisi();
        String sql = "delete from punyakomposisi "
                + "where kodeobat = '" + obat + "' and idkomposisi='"+ kom+"'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    public void deleteIndikasi(PunyaIndikasi in) throws SQLException {
        String obat = in.getObat().getKodeObat();
        String indk = in.getIndikasi().getIdIndikasi();
        String sql = "delete from punyaindikasi "
                + "where kodeobat = '" + obat + "' and idindikasi='"+ indk+"'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    public void deleteKontraIndikasi(PunyaKontraIndikasi kon) throws SQLException {
        String obat = kon.getObat().getKodeObat();
        String kontr = kon.getKontraIndikasi().getIdKontraIndikasi();
        String sql = "delete from punyakontraindikasi "
                + "where kodeobat = '" + obat + "' and idkontraindikasi='"+ kontr+"'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    public void deletePerhatian(PunyaPerhatian pe) throws SQLException {
        String obat = pe.getObat().getKodeObat();
        String per = pe.getPerhatian().getIdPerhatian();
        String sql = "delete from punyaperhatian "
                + "where kodeobat = '" + obat + "' and idperhatian='"+ per+"'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    public void deleteEfek(PunyaEfekSamping ef) throws SQLException {
        String obat = ef.getObat().getKodeObat();
        String efek = ef.getEfekSamping().getIdEfekSamping();
        String sql = "delete from punyaefeksamping "
                + "where kodeobat = '" + obat + "' and idefeksamping='"+ efek+"'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    public void deletekemasan(PunyaKemasan ke) throws SQLException {
        String obat = ke.getObat().getKodeObat();
        String kem = ke.getKemasan().getIdKemasan();
        String sql = "delete from punyakemasan "
                + "where kodeobat = '" + obat + "' and idkemasan='"+ kem+"'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }
    public void deletedosis(PunyaDosis dos) throws SQLException {
        String obat = dos.getObat().getKodeObat();
        String dosis = dos.getDosis().getIdDosis();
        String sql = "delete from punyadosis "
                + "where kodeobat = '" + obat + "' and iddosis='"+ dosis+"'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }


    public void deletePunyaDosis(PunyaDosis punyaDosis) throws SQLException {
        String kodeObat = punyaDosis.getObat().getKodeObat();
        String sql = "delete from PunyaDosis "
                + "where kodeObat = '" + kodeObat + "'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deletePunyaEfekSamping(PunyaEfekSamping punyaEfekSamping) throws SQLException {
        String kodeObat = punyaEfekSamping.getObat().getKodeObat();
        String sql = "delete from PunyaEfekSamping "
                + "where kodeObat = '" + kodeObat + "'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deletePunyaIndikasi(PunyaIndikasi punyaIndikasi) throws SQLException {
        String kodeObat = punyaIndikasi.getObat().getKodeObat();
        String sql = "delete from PunyaIndikasi "
                + "where kodeObat = '" + kodeObat + "'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deletePunyaKemasan(PunyaKemasan punyaKemasan) throws SQLException {
        String kodeObat = punyaKemasan.getObat().getKodeObat();
        String sql = "delete from PunyaKemasan "
                + "where kodeObat = '" + kodeObat + "'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deletePunyaKomposisi(PunyaKomposisi punyaKomposisi) throws SQLException {
        String kodeObat = punyaKomposisi.getObat().getKodeObat();
        String sql = "delete from PunyaKomposisi "
                + "where kodeObat = '" + kodeObat + "'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deletePunyaKontraIndikasi(PunyaKontraIndikasi punyaKontraIndikasi) throws SQLException {
        String kodeObat = punyaKontraIndikasi.getObat().getKodeObat();
        String sql = "delete from PunyaKontraIndikasi "
                + "where kodeObat = '" + kodeObat + "'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deletePunyaPerhatian(PunyaPerhatian punyaPerhatian) throws SQLException {
        String kodeObat = punyaPerhatian.getObat().getKodeObat();
        String sql = "delete from PunyaPerhatian "
                + "where kodeObat = '" + kodeObat + "'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

    public void deleteObat(Obat obat) throws SQLException {
        String kodeObat = obat.getKodeObat();
        String sql = "delete from Obat "
                + "where kodeObat = '" + kodeObat + "'" ;
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

     public void updateObat(Obat obat) throws SQLException {
        String kode = obat.getKodeObat();
        String nama = obat.getNamaObat();
        String kodeKat = obat.getKategori().getKodeKategori();
        String sql = "update obat set namaobat = '" + nama + "', "
                + "kodekategori = '" + kodeKat + "' "
                + " where kodeobat = '" + kode + "'";
        Statement stat = conn.createStatement();
        stat.executeUpdate(sql);
        conn.close();
    }

}
